package com.fuershiye.woman.monitor.dao;

import java.math.BigInteger;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fuershiye.woman.monitor.beans.IdName;
import com.fuershiye.woman.monitor.beans.Page;
import com.fuershiye.woman.monitor.model.FormsDepart;
import com.fuershiye.woman.monitor.utils.DaoSqlUtil;
import com.google.common.collect.Lists;

@Repository("formsDepartDao")
@SuppressWarnings("unchecked")
public class FormsDepartDao {

    @Autowired
    private HibernateDao hibernateDao;

    public void save(FormsDepart it){
        hibernateDao.save(it);
    }

    public void delete(Long id) {

        String sql = "update forms_depart set status = 0 where id = ?";
        SQLQuery query = hibernateDao.getSqlQuery(sql).addEntity(FormsDepart.class);
        query.setLong(0, id);
        query.executeUpdate();
    }

    public FormsDepart get(Long id) {
    	return hibernateDao.getById(FormsDepart.class,id);
    }

    @SuppressWarnings("unchecked")
    public FormsDepart getTop() {

        String hql = "from FormsDepart ORDER BY id DESC";
        Query query = hibernateDao.getQuery(hql);
        query.setFirstResult(0);
        query.setMaxResults(1);
        List<FormsDepart> result = query.list();
        if(result==null||result.size()<1){
        	return null;
        }
        return result.get(0);
    }
    
    @SuppressWarnings("unchecked")
    public FormsDepart findBySort(Long pos, Integer aim, Integer count) {
		
    	StringBuffer sb = new StringBuffer("from FormsDepart where sort "); 
		if(aim==1){
			sb.append(">:sort ");
		}else{
			sb.append("<:sort ");
		}
		sb.append("order by sort desc");
		
        Query query = hibernateDao.getQuery(sb.toString());
        query.setParameter("sort", pos);
        query.setFirstResult(0);
        query.setMaxResults(count);
        
        List<FormsDepart> result = query.list();
        if(result==null||result.size()<1){
        	return null;
        }
        return result.get(0);
    }
    
    @SuppressWarnings("unchecked")
    public List<IdName> getIdNameByDepart(Long depart, Integer year, Long forms) {

        List<IdName> result = Lists.newArrayList();
        if(depart==null||year==null||forms==null){
        	return result;
        }

        StringBuffer sb = new StringBuffer("select id,status from forms_depart as f where 1=1 ");
        sb.append("and departid="+depart+" ");
        sb.append("and year="+year+" ");
        sb.append("and formsid="+forms+" ");
        sb.append("ORDER BY sort DESC ");
    	Query query = hibernateDao.getSqlQuery(sb.toString());
    	
        List<Object[]> queryResult = query.list();
        
        for (Object[] fields : queryResult) {
        	IdName idAndName = new IdName();
        	idAndName.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
        	result.add(idAndName);
		}
	  	return result;
    }
    
    @SuppressWarnings("unchecked")
	public FormsDepart findByCondtion(Long areaid, Long departid, Long formsid, Integer year, Integer status) {
    	
    	StringBuffer sb = new StringBuffer("from FormsDepart where 1=1 ");
    	sb = DaoSqlUtil.appendSql(sb, "areaid", areaid);
    	sb = DaoSqlUtil.appendSql(sb, "departid", departid);
    	sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
    	sb = DaoSqlUtil.appendSql(sb, "year", year);
    	sb = DaoSqlUtil.appendSql(sb, "status", status);
    	sb.append("ORDER BY sort DESC ");
    	
    	Query query = hibernateDao.getQuery(sb.toString());
    	query = DaoSqlUtil.appendQuery(query, "areaid", areaid);
    	query = DaoSqlUtil.appendQuery(query, "departid", departid);
    	query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
    	query = DaoSqlUtil.appendQuery(query, "year", year);
    	query = DaoSqlUtil.appendQuery(query, "status", status);

    	List<FormsDepart> result = query.list();
        if(result==null||result.size()<1){
        	return null;
        }
        return result.get(0);
    }
    
    @SuppressWarnings("unchecked")
    public List<FormsDepart> listByCondtion(List<Long> areaid, List<Long> departid, List<Integer> year, Long formsid, Integer status, Page page) {
    	
    	StringBuffer sb = new StringBuffer("from FormsDepart where 1=1 ");
    	sb = DaoSqlUtil.appendSqlListLong(sb, "areaid", areaid);
    	sb = DaoSqlUtil.appendSqlListLong(sb, "departid", departid);
    	sb = DaoSqlUtil.appendSqlListInt(sb, "year", year);
    	sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
    	sb = DaoSqlUtil.appendSql(sb, "status", status);
    	sb.append("ORDER BY sort DESC ");
    	
    	Query query = hibernateDao.getQuery(sb.toString());
    	
    	query = DaoSqlUtil.appendQueryListLong(query, "areaid", areaid);
    	query = DaoSqlUtil.appendQueryListLong(query, "departid", departid);
    	query = DaoSqlUtil.appendQueryListInt(query, "year", year);
    	query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
    	query = DaoSqlUtil.appendQuery(query, "status", status);
    	
    	if(page!=null){
    		query.setFirstResult(page.getStart());
    		query.setMaxResults(page.getPageSize()); 
    	}
    	
    	return query.list();
    }

	public List<FormsDepart> listByCondtionStatusOpposite(List<Long> areaid, List<Long> departid, List<Integer> year, Long formsid, Integer status, Page page) {

		StringBuffer sb = new StringBuffer("from FormsDepart where 1=1 ");
		sb = DaoSqlUtil.appendSqlListLong(sb, "areaid", areaid);
		sb = DaoSqlUtil.appendSqlListLong(sb, "departid", departid);
		sb = DaoSqlUtil.appendSqlListInt(sb, "year", year);
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSqlOpposite(sb, "status", status);
		sb.append("ORDER BY sort DESC ");

		Query query = hibernateDao.getQuery(sb.toString());

		query = DaoSqlUtil.appendQueryListLong(query, "areaid", areaid);
		query = DaoSqlUtil.appendQueryListLong(query, "departid", departid);
		query = DaoSqlUtil.appendQueryListInt(query, "year", year);
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		if(page!=null){
			query.setFirstResult(page.getStart());
			query.setMaxResults(page.getPageSize());
		}

		return query.list();
	}
    
    public int countByCondtion(List<Long> areaid, List<Long> departid, List<Integer> year, Long formsid, Integer status) {
    	
    	StringBuffer sb = new StringBuffer("select count(*) from forms_depart where 1=1 ");

    	sb = DaoSqlUtil.appendSqlListLong(sb, "areaid", areaid);
    	sb = DaoSqlUtil.appendSqlListLong(sb, "departid", departid);
    	sb = DaoSqlUtil.appendSqlListInt(sb, "year", year);
    	sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
    	sb = DaoSqlUtil.appendSql(sb, "status", status);
    	sb.append("ORDER BY sort DESC ");
    	
    	Query query = hibernateDao.getSqlQuery(sb.toString());
    	
    	query = DaoSqlUtil.appendQueryListLong(query, "areaid", areaid);
    	query = DaoSqlUtil.appendQueryListLong(query, "departid", departid);
    	query = DaoSqlUtil.appendQueryListInt(query, "year", year);
    	query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
    	query = DaoSqlUtil.appendQuery(query, "status", status);
    	
		BigInteger count = (BigInteger) query.uniqueResult();
		return (int) count.longValue();
    }


	public int countByCondtionStatusOpposite(List<Long> areaid, List<Long> departid, List<Integer> year, Long formsid, Integer status) {

		StringBuffer sb = new StringBuffer("select count(*) from forms_depart where 1=1 ");

		sb = DaoSqlUtil.appendSqlListLong(sb, "areaid", areaid);
		sb = DaoSqlUtil.appendSqlListLong(sb, "departid", departid);
		sb = DaoSqlUtil.appendSqlListInt(sb, "year", year);
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSqlOpposite(sb, "status", status);
		sb.append("ORDER BY sort DESC ");

		Query query = hibernateDao.getSqlQuery(sb.toString());

		query = DaoSqlUtil.appendQueryListLong(query, "areaid", areaid);
		query = DaoSqlUtil.appendQueryListLong(query, "departid", departid);
		query = DaoSqlUtil.appendQueryListInt(query, "year", year);
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		BigInteger count = (BigInteger) query.uniqueResult();
		return (int) count.longValue();
	}

}
